<?php
/**
 * It runs for hourly tasks.
 * 
 *   1. Send offer notification for each user.
 *
 * LBB Solutions Oy 版权所有 © 2013
 * 
 * ---------------------------------------------------------------------
 * 2013.10.21    Bocheng        Improved the pushing_log checking in SQL
 *                              never for device which the offer has been pushed to
 */
class NotifyHourlyCommand extends CConsoleCommand {
	
	/**
	 * @param array $args
	 *   1. prod | debug
	 */
	public function run($args) {
		echo 'Running NotifyHourlyCommand: ' . print_r($args, true);
		Yii::app()->setTimeZone('Europe/Helsinki');
		
		$debug = strtolower($args[0]);
		if ($debug != 'prod') {
			// set the differnet DB
			Yii::app()->db->setActive(false);
			Yii::app()->db->connectionString = 'mysql:host=localhost;dbname=yamiifi_dev';
		}
		
		$current_hour = (int)date('G');
		if ($current_hour > 9 and $current_hour < 21) {
			//only run this 10:00 - 20:00
			echo 'The current hour is ' . $current_hour . ". A push notification time (From 10:00 to 20:00).\n";
			$this->notifyOffers($debug != 'prod');
		} else {
			echo 'The current hour is ' . $current_hour . ". Not a push notification time.\n";
		}
	}
	
	public function notifyOffers($isDevp) {
		//1. get all users who has the notification token and device type
		$Devices = $this->_fetchDevices();
		
		//2. create reporting pictures for each merchant
		$Notifications = $this->_makeNotifications($Devices);
		
		$count = 0;
		if (count($Notifications) < 1) {
			return $count;
		}
		
		//3. push notification
		$loged_push = null;
		$connection = Yii::app()->db;
		foreach ($Notifications as $k => $v) {
			$messageData = array(
				'message' => $v['message'],
				'offer_id' => $v['offer_id'],
			);
			
			$success = BUtils::PushNotificationToDevice($v['device_notification_token'], $v['device_type'], $messageData, $isDevp);
			
			//logging
			if ($success == 1) {
				$count ++;
				
				$loged_push .= "[{$k}]: [{$v['device_type']}], [{$v['device_token']}], {$v['device_notification_token']}, {$v['id_user']}, {$v['min_discount']}, {$v['max_distance']}, {$v['distance']}, [offer: {$v['offer_id']}]\n";

				// save to table: tbl_user_device_pushing_log
				$sql_pushing_log = 'INSERT INTO tbl_user_device_pushing_log (device_token, device_type, id_special, id_user, distance)
					VALUES(:device_token, :device_type, :id_special, :id_user, :distance)';
				$params_pushing = array(
					':device_token' => $v['device_token'],
					':device_type' => $v['device_type'],
					':id_special' => $v['offer_id'],
					':id_user' => $v['id_user'],
					':distance' => $v['distance'],
				);
				$command = $connection->createCommand($sql_pushing_log);
				$command->execute($params_pushing);
			}
		}
		$connection->active = false;
		
		if (!empty($loged_push)) {
			BUtils::EmailAdmin('Push '. $count .' notifications at ' . date('Y-m-d H:i:s'), $loged_push);
			echo $loged_push;
		}
	}
	
	/**
	 * Fetch device info we can push offer
	 * 
	 * @return array
	 */
	protected function _fetchDevices() {
		$sql_devices = "SELECT device_token, device_type, device_notification_token, id_user, min_discount, max_distance, lang, latitude, longitude
			FROM tbl_user_device_settings
			WHERE device_type IN ('ios','android') AND ABS(latitude)>0 AND ABS(longitude)>0
			AND (device_notification_token IS NOT NULL OR 
			     (device_notification_token IS NULL AND device_type='ios' AND device_token NOT IN (
					SELECT device_notification_token FROM tbl_user_device_settings WHERE device_notification_token IS NOT NULL) ) )
			ORDER BY insert_time DESC";
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_devices);
		$rows = $command->queryAll(true);
		$connection->active = false;
		
		return $rows;
	}
	
	/**
	 * Fetch device and message.
	 * @param array $Devices
	 * @return array
	 */
	protected function _makeNotifications($Devices) {
		$Notifications = array();
		
		$connection = Yii::app()->db;
		foreach ($Devices as $k => $v) {
			if (empty($v['device_notification_token'])) {
				if (strlen($v['device_token']) === 64) {
					$v['device_notification_token'] = $v['device_token']; //old notification id
				} else {
					continue;//not old notification id
				}
			}
			
			$item = $v;
			
			$params = array();

			// get distance
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$v['latitude'];
			$params[':lng'] = (double)$v['longitude'];
			
			// has user_id: no claimed/redeemed + distance + discount + following
			$s_status = '';
			$s_having_status = ' AND distance<=:distance AND discount>=:discount ';
			$params[':distance'] = $v['max_distance'] / 2; // transfer the distance
			$params[':discount'] = abs($v['min_discount']);
			if (!empty($v['id_user'])) {
				$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
					WHERE r3.id_special=s.id AND r3.id_user=:id_user AND DATE(r3.insert_timestamp)=DATE(NOW())
					ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
					, (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
					   WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following ';	// user follows the merchant
				$params[':id_user'] = $v['id_user'];
				
				// no claimed/redeemed + distance + discount + following
				$s_having_status = ' AND (redeemed_status IS NULL OR redeemed_status=0) AND (distance<=:distance AND discount>=:discount OR following>0) ';
			}

			// check if the offer should be pushed to device
			// fetch specials
			// comments: only care today's claimed number: DATE(r1.insert_timestamp)=DATE(NOW())
			$params[':week_days'] = '%'.date('N').'%';	//week_days => 5
			$params[':hour_min'] = (int)date('Gi');		//hour_min => 934
			$params[':device_token'] = $v['device_token'];	//get the device ID (UUID)
			$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.start_hour, s.end_hour, s.week_days, s.price, s.value, s.type,
				1-(price/value) as discount,
				' . $s_distance . '
				UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date,
				s.max_number,
				m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude, '
				. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
					 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
				. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
					 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
				. $s_status . '
				FROM tbl_merchant_special s '
				. ' INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant '
				. ' WHERE s.status=1 AND s.trash=0 AND s.content IS NOT NULL AND s.start_date<NOW() AND s.end_date>NOW() AND s.value>0 '
				. '   AND (SELECT COUNT(*) FROM tbl_user_device_pushing_log WHERE device_token=:device_token AND id_special=s.id) < 1' //never for device which the offer has been pushed to
				. '   AND (s.week_days LIKE :week_days AND s.start_hour<=:hour_min AND s.end_hour>=:hour_min) '	//for today and valid time checking
				. ' GROUP BY s.id
					HAVING (claimed_num<s.max_number OR claimed_num IS NULL OR s.max_number=0 OR s.max_number IS NULL) 
					   ' . $s_having_status
				. ' ORDER BY distance ASC, s.published_time DESC
					LIMIT 1';//only 1 row
			$command = $connection->createCommand($sql_specials);
			//$rows = $command->queryAll(true, $params);
			$row = $command->queryRow(true, $params);
			
			if (empty($row['id'])) {
				continue;
			}
			
			/////////////////////////////////////////
			// make message data
			$tmpl['fi'] = 'Uusi tarjous löytyy: {merchant_name}, {discount}, ({distance}).';
			$tmpl['en'] = 'New special found: {discount} at {merchant_name} ({distance}).';
			$discount = 1 - ((float)$row['price']/(float)$row['value']);
			$params = array(
				'{discount}' => '-' . (round($discount, 2) * 100) . '%',
				'{merchant_name}' => $row['name'],
				'{distance}' => round($row['distance'], 1) . 'km',
			);
			if ($row['distance'] < 1) {
				$params['{distance}'] = round($row['distance']*1000, 0) . 'm';
			}
			switch (strtolower($v['lang'])) {
				case 'fi':
				case 'fi_fi':
					$message = str_replace(array_keys($params), array_values($params), $tmpl['fi']);
					break;
				default:
					$message = str_replace(array_keys($params), array_values($params), $tmpl['en']);
					break;
			}
			//make the messageData
			$item['message'] = $message;
			$item['offer_id'] = $row['id'];
			$item['distance'] = $row['distance'];
			
			$Notifications[] = $item;
		}
		$connection->active = false;
		
		return $Notifications;
	}
}